﻿using QLHS.DTO;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace QLHS.DataLayer
{
    public class QuyDinhDAO
    {
        public BindingList<QuyDinhDTO> LoadAll()
        {
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            string sql = "select * from QUYDINH";
            BindingList<QuyDinhDTO> QuyDinhTable = new BindingList<QuyDinhDTO>();

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int _STT = Convert.ToInt32(reader["STT"].ToString());
                        string _TenQD = reader["TenQD"].ToString();
                        string _GiaTri = reader["GiaTri"].ToString();
                        string _KieuGiaTri = reader["KieuGiaTri"].ToString();
                       
                        QuyDinhDTO temp = new QuyDinhDTO
                        {
                            STT = _STT,
                            TenQD = _TenQD,
                            GiaTri = _GiaTri,
                            KieuGiaTri = _KieuGiaTri
                        };
                        QuyDinhTable.Add(temp);
                    }
                }
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
            return QuyDinhTable;
        }

        public void Delete(QuyDinhDTO del)
        {
            string sql = string.Format("delete from QUYDINH where STT = {0}", del.STT);
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }

        public void Insert(QuyDinhDTO InsertObject)
        {
            string sql = string.Format("insert into QUYDINH values (N'{0}', N'{1}', N'{2}')",
                InsertObject.TenQD, InsertObject.GiaTri, InsertObject.KieuGiaTri);
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }

        public void Update(QuyDinhDTO UpdateObject)
        {
            string sql = string.Format("update QUYDINH set TenQD = N'{1}' , GiaTri = N'{2}', KieuGiaTri = N'{3}' where STT = {0}",
                UpdateObject.STT,
                UpdateObject.TenQD,
                UpdateObject.GiaTri,
                UpdateObject.KieuGiaTri);
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }

        //public void ChangeKeyValue(string Old_MSMH, string New_MSMH)
        //{
        //    string sql = string.Format("update MonHoc set MSMH = '{0}' where (MSMH = '{1}')", New_MSMH, Old_MSMH);

        //    SqlConnection Cn = new SqlConnection(Helper.ConnectionString);
        //    SqlCommand Cmd = new SqlCommand(sql, Cn);
        //    Cn.Open();
        //    Cmd.ExecuteNonQuery();
        //    Cn.Close();
        //}

        public BindingList<QuyDinhDTO> GetQuyDinhDataByPrimaryKey(int STT)
        {
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            string sql = string.Format("select* from QUYDINH where STT = {0}", STT);
            BindingList<QuyDinhDTO> Result = new BindingList<QuyDinhDTO>();

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int _STT = Convert.ToInt32(reader["STT"].ToString());
                        string _TenQD = reader["TenQD"].ToString();
                        string _GiaTri = reader["GiaTri"].ToString();
                        string _KieuGiaTri = reader["KieuGiaTri"].ToString();

                        QuyDinhDTO temp = new QuyDinhDTO
                        {
                            STT = _STT,
                            TenQD = _TenQD,
                            GiaTri = _GiaTri,
                            KieuGiaTri = _KieuGiaTri
                        };
                        Result.Add(temp);
                    }
                }
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
            return Result;
        }


        public string STT { get; set; }
    }
}
